<!DOCTYPE html><html lang="en"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1"><meta name="format-detection" content="telephone=no"><meta name="apple-mobile-web-app-capable" content="yes"><meta name="apple-mobile-web-app-status-bar-style" content="black"><link rel="icon" href="/images/touxiang1.png?v=2.6.1" type="image/png" sizes="16x16"><link rel="icon" href="/images/touxiang1.png?v=2.6.1" type="image/png" sizes="32x32"><meta name="description" content="Mysql                           DB       数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。                     DBMS       数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL_01">
<meta property="og:url" content="https://gityang2016.gitee.io/2021/01/27/mysql_01/index.html">
<meta property="og:site_name" content="Yangj&#39;s blog">
<meta property="og:description" content="Mysql                           DB       数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。                     DBMS       数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器">
<meta property="og:locale" content="en_US">
<meta property="article:published_time" content="2021-01-26T16:09:38.624Z">
<meta property="article:modified_time" content="2021-01-26T17:21:42.483Z">
<meta property="article:author" content="Yangj">
<meta property="article:tag" content="sql">
<meta name="twitter:card" content="summary"><title>MySQL_01 | Yangj's blog</title><link ref="canonical" href="https://gityang2016.gitee.io/2021/01/27/mysql_01/"><link rel="dns-prefetch" href="https://cdn.jsdelivr.net"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.12.1/css/all.min.css" type="text/css"><link rel="stylesheet" href="/css/index.css?v=2.6.1"><script>var Stun = window.Stun || {};
var CONFIG = {
  root: '/',
  algolia: undefined,
  assistSearch: undefined,
  fontIcon: {"prompt":{"success":"fas fa-check-circle","info":"fas fa-arrow-circle-right","warning":"fas fa-exclamation-circle","error":"fas fa-times-circle"},"copyBtn":"fas fa-copy"},
  sidebar: {"offsetTop":"20px","tocMaxDepth":6},
  header: {"enable":true,"showOnPost":true,"scrollDownIcon":true},
  postWidget: {"endText":true},
  nightMode: {"enable":true},
  back2top: {"enable":true},
  codeblock: {"style":"default","highlight":"light","wordWrap":false},
  reward: false,
  fancybox: false,
  zoomImage: {"gapAside":"20px"},
  galleryWaterfall: undefined,
  lazyload: false,
  pjax: undefined,
  externalLink: {"icon":{"enable":true,"name":"fas fa-external-link-alt"}},
  shortcuts: undefined,
  prompt: {"copyButton":"Copy","copySuccess":"Copy Success","copyError":"Copy Error"},
  sourcePath: {"js":"js","css":"css","images":"images"},
};

window.CONFIG = CONFIG;</script><meta name="generator" content="Hexo 5.3.0"><link rel="alternate" href="/atom.xml" title="Yangj's blog" type="application/atom+xml">
</head><body><div class="container" id="container"><header class="header" id="header"><div class="header-inner"><nav class="header-nav header-nav--fixed"><div class="header-nav-inner"><div class="header-nav-menubtn"><i class="fas fa-bars"></i></div><div class="header-nav-menu"><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/"><span class="header-nav-menu-item__icon"><i class="fas fa-home"></i></span><span class="header-nav-menu-item__text">Home</span></a></div><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/archives/"><span class="header-nav-menu-item__icon"><i class="fas fa-folder-open"></i></span><span class="header-nav-menu-item__text">Archives</span></a></div><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/categories/"><span class="header-nav-menu-item__icon"><i class="fas fa-layer-group"></i></span><span class="header-nav-menu-item__text">Categories</span></a></div><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/tags/"><span class="header-nav-menu-item__icon"><i class="fas fa-tags"></i></span><span class="header-nav-menu-item__text">Tags</span></a></div><div class="header-nav-menu-item"><a class="header-nav-menu-item__link" href="/aboutme/"><span class="header-nav-menu-item__icon"><i class="fas fa-user-tag"></i></span><span class="header-nav-menu-item__text">About</span></a></div></div><div class="header-nav-mode"><div class="mode"><div class="mode-track"><span class="mode-track-moon"></span><span class="mode-track-sun"></span></div><div class="mode-thumb"></div></div></div></div></nav><div class="header-banner"><div class="header-banner-info"><div class="header-banner-info__title">Yangj's blog</div><div class="header-banner-info__subtitle">It is never too late to mend</div></div><div class="header-banner-arrow"><div class="header-banner-arrow__icon"><i class="fas fa-angle-down"></i></div></div></div></div></header><main class="main" id="main"><div class="main-inner"><div class="content-wrap" id="content-wrap"><div class="content" id="content"><!-- Just used to judge whether it is an article page--><div id="is-post"></div><div class="post"><header class="post-header"><h1 class="post-title">MySQL_01</h1><div class="post-meta"><span class="post-meta-item post-meta-item--createtime"><span class="post-meta-item__icon"><i class="far fa-calendar-plus"></i></span><span class="post-meta-item__info">Created</span><span class="post-meta-item__value">2021-01-27</span></span><span class="post-meta-item post-meta-item--updatetime"><span class="post-meta-item__icon"><i class="far fa-calendar-check"></i></span><span class="post-meta-item__info">Updated</span><span class="post-meta-item__value">2021-01-27</span></span></div></header><div class="post-body">
        <h1 id="Mysql"   >
          <a href="#Mysql" class="heading-link"><i class="fas fa-link"></i></a>Mysql</h1>
      
        <h2 id="DB"   >
          <a href="#DB" class="heading-link"><i class="fas fa-link"></i></a>DB</h2>
      <p>数据库（database）：存储数据的“仓库”。它保存了一系列有组织的数据。</p>

        <h2 id="DBMS"   >
          <a href="#DBMS" class="heading-link"><i class="fas fa-link"></i></a>DBMS</h2>
      <p>数据库管理系统（Database Management System）。数据库是通过DBMS创建和操作的容器</p>

        <h2 id="SQL"   >
          <a href="#SQL" class="heading-link"><i class="fas fa-link"></i></a>SQL</h2>
      <p>结构化查询语言（Structure Query Language）：专门用来与数据库通信的语言。</p>

        <h2 id="MySQL产品的介绍和安装"   >
          <a href="#MySQL产品的介绍和安装" class="heading-link"><i class="fas fa-link"></i></a>MySQL产品的介绍和安装</h2>
      
        <h3 id="MySQL服务的启动和停止"   >
          <a href="#MySQL服务的启动和停止" class="heading-link"><i class="fas fa-link"></i></a>MySQL服务的启动和停止</h3>
      <pre><code>方式一：计算机——右击管理——服务
方式二：通过管理员身份运行
net start 服务名（启动服务）
net stop 服务名（停止服务）</code></pre>

        <h3 id="MySQL服务的登录和退出"   >
          <a href="#MySQL服务的登录和退出" class="heading-link"><i class="fas fa-link"></i></a>MySQL服务的登录和退出</h3>
      <pre><code>方式一：通过mysql自带的客户端
只限于root用户

方式二：通过windows自带的客户端
登录：
mysql 【-h主机名 -P端口号 】-u用户名 -p密码

退出：
exit或ctrl+C</code></pre>

        <h2 id="MySQL的常见命令"   >
          <a href="#MySQL的常见命令" class="heading-link"><i class="fas fa-link"></i></a>MySQL的常见命令</h2>
      <pre><code>1.查看当前所有的数据库
show databases;
2.打开指定的库
use 库名
3.查看当前库的所有表
show tables;
4.查看其它库的所有表
show tables from 库名;
5.创建表
create table 表名(

    列名 列类型,
    列名 列类型，
    。。。
);
6.查看表结构
desc 表名;

7.查看服务器的版本
方式一：登录到mysql服务端
select version();
方式二：没有登录到mysql服务端
mysql --version
或
mysql -V</code></pre>

        <h2 id="SQL的常见命令"   >
          <a href="#SQL的常见命令" class="heading-link"><i class="fas fa-link"></i></a>SQL的常见命令</h2>
      <pre><code>show databases； 查看所有的数据库
use 库名； 打开指定 的库
show tables ; 显示库中的所有表
show tables from 库名;显示指定库中的所有表
create table 表名(
    字段名 字段类型,    
    字段名 字段类型
); 创建表

desc 表名; 查看指定表的结构
select * from 表名;显示表中的所有数据</code></pre>

        <h2 id="进阶1：基础查询"   >
          <a href="#进阶1：基础查询" class="heading-link"><i class="fas fa-link"></i></a>进阶1：基础查询</h2>
      <p>语法：<br>select 查询列表 from 表名;</p>
<p>USE myemployees;</p>

        <h3 id="1-查询表中的单个字段"   >
          <a href="#1-查询表中的单个字段" class="heading-link"><i class="fas fa-link"></i></a>1.查询表中的单个字段</h3>
      <p>SELECT last_name FROM employees;</p>

        <h3 id="2-查询表中的多个字段"   >
          <a href="#2-查询表中的多个字段" class="heading-link"><i class="fas fa-link"></i></a>2.查询表中的多个字段</h3>
      <p>SELECT last_name,salary,email FROM employees;</p>

        <h3 id="3-查询表中的所有字段"   >
          <a href="#3-查询表中的所有字段" class="heading-link"><i class="fas fa-link"></i></a>3.查询表中的所有字段</h3>
      <p>方式一：</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">    `employee_id`,</span><br><span class="line">    `first_name`,</span><br><span class="line">    `last_name`,</span><br><span class="line">    `phone_number`,</span><br><span class="line">    `last_name`,</span><br><span class="line">    `job_id`,</span><br><span class="line">    `phone_number`,</span><br><span class="line">    `job_id`,</span><br><span class="line">    `salary`,</span><br><span class="line">    `commission_pct`,</span><br><span class="line">    `manager_id`,</span><br><span class="line">    `department_id`,</span><br><span class="line">    `hiredate` </span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">    employees ;</span><br></pre></td></tr></table></div></figure>
<p> 方式二：<br> SELECT * FROM employees;</p>

        <h3 id="4-查询常量值"   >
          <a href="#4-查询常量值" class="heading-link"><i class="fas fa-link"></i></a>4.查询常量值</h3>
      <p> SELECT 100;<br> SELECT ‘john’;</p>

        <h3 id="5-查询表达式"   >
          <a href="#5-查询表达式" class="heading-link"><i class="fas fa-link"></i></a>5.查询表达式</h3>
      <p> SELECT 100%98;</p>

        <h3 id="6-查询函数"   >
          <a href="#6-查询函数" class="heading-link"><i class="fas fa-link"></i></a>6.查询函数</h3>
      <p> SELECT VERSION();</p>

        <h3 id="7-起别名"   >
          <a href="#7-起别名" class="heading-link"><i class="fas fa-link"></i></a>7.起别名</h3>
       <figure class="highlight plain"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">①便于理解</span><br><span class="line">②如果要查询的字段有重名的情况，使用别名可以区分开来</span><br></pre></td></tr></table></div></figure>
<p> 方式一：使用as<br> <figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="number">100</span><span class="operator">%</span><span class="number">98</span> <span class="keyword">AS</span> 结果;</span><br><span class="line"><span class="keyword">SELECT</span> last_name <span class="keyword">AS</span> 姓,first_name <span class="keyword">AS</span> 名 <span class="keyword">FROM</span> employees;</span><br></pre></td></tr></table></div></figure><br>方式二：使用空格<br> <figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> last_name 姓,first_name 名 <span class="keyword">FROM</span> employees;</span><br></pre></td></tr></table></div></figure><br>#案例：查询salary，显示结果为 out put<br> <figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> salary <span class="keyword">AS</span> &quot;out put&quot; <span class="keyword">FROM</span> employees;</span><br></pre></td></tr></table></div></figure></p>

        <h3 id="8-去重"   >
          <a href="#8-去重" class="heading-link"><i class="fas fa-link"></i></a>8.去重</h3>
      <p>案例：查询员工表中涉及到的所有的部门编号<br> <figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="keyword">DISTINCT</span> department_id <span class="keyword">FROM</span> employees;</span><br></pre></td></tr></table></div></figure></p>

        <h3 id="9-号的作用"   >
          <a href="#9-号的作用" class="heading-link"><i class="fas fa-link"></i></a>9.+号的作用</h3>
      <p>/*</p>
<p>java中的+号：<br>①运算符，两个操作数都为数值型<br>②连接符，只要有一个操作数为字符串</p>
<p>mysql中的+号：<br>仅仅只有一个功能：运算符</p>
<p>select 100+90; 两个操作数都为数值型，则做加法运算<br>select ‘123’+90;只要其中一方为字符型，试图将字符型数值转换成数值型。如果转换成功，则继续做加法运算<br>select ‘john’+90;    如果转换失败，则将字符型数值转换成0</p>
<p>select null+10; 只要其中一方为null，则结果肯定为null</p>
<p>*/</p>
<p>案例：查询员工名和姓连接成一个字段，并显示为 姓名</p>
 <figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> CONCAT(<span class="string">&#x27;a&#x27;</span>,<span class="string">&#x27;b&#x27;</span>,<span class="string">&#x27;c&#x27;</span>) <span class="keyword">AS</span> 结果;</span><br><span class="line"></span><br><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">	CONCAT(last_name,first_name) <span class="keyword">AS</span> 姓名</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees;</span><br></pre></td></tr></table></div></figure>

        <h2 id="进阶2：条件查询"   >
          <a href="#进阶2：条件查询" class="heading-link"><i class="fas fa-link"></i></a>进阶2：条件查询</h2>
      <p>/*</p>
<p>语法：<br>    select<br>        查询列表<br>    from<br>        表名<br>    where<br>        筛选条件;</p>
<p>分类：</p>

        <h3 id="一、按条件表达式筛选"   >
          <a href="#一、按条件表达式筛选" class="heading-link"><i class="fas fa-link"></i></a>一、按条件表达式筛选</h3>
      <pre><code>简单条件运算符：&gt; &lt; = != &lt;&gt; &gt;= &lt;=</code></pre>

        <h3 id="二、按逻辑表达式筛选"   >
          <a href="#二、按逻辑表达式筛选" class="heading-link"><i class="fas fa-link"></i></a>二、按逻辑表达式筛选</h3>
      <pre><code>逻辑运算符：
作用：用于连接条件表达式
    &amp;&amp; || !
    and or not

&amp;&amp;和and：两个条件都为true，结果为true，反之为false
||或or： 只要有一个条件为true，结果为true，反之为false
!或not： 如果连接的条件本身为false，结果为true，反之为false</code></pre>

        <h3 id="三、模糊查询"   >
          <a href="#三、模糊查询" class="heading-link"><i class="fas fa-link"></i></a>三、模糊查询</h3>
      <pre><code>    like
    between and
    in
    is null</code></pre>
<p>*/</p>

        <h3 id="示例一、按条件表达式筛选"   >
          <a href="#示例一、按条件表达式筛选" class="heading-link"><i class="fas fa-link"></i></a>示例一、按条件表达式筛选</h3>
      <p>案例1：查询工资&gt;12000的员工信息</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">	<span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	salary<span class="operator">&gt;</span><span class="number">12000</span>;</span><br></pre></td></tr></table></div></figure>

<p>案例2：查询部门编号不等于90号的员工名和部门编号</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> </span><br><span class="line">	last_name,</span><br><span class="line">	department_id</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	department_id<span class="operator">&lt;&gt;</span><span class="number">90</span>;</span><br></pre></td></tr></table></div></figure>

        <h3 id="二、按逻辑表达式筛选-1"   >
          <a href="#二、按逻辑表达式筛选-1" class="heading-link"><i class="fas fa-link"></i></a>二、按逻辑表达式筛选</h3>
      <p>#案例1：查询工资z在10000到20000之间的员工名、工资以及奖金</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	last_name,</span><br><span class="line">	salary,</span><br><span class="line">	commission_pct</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	salary<span class="operator">&gt;=</span><span class="number">10000</span> <span class="keyword">AND</span> salary<span class="operator">&lt;=</span><span class="number">20000</span>;</span><br></pre></td></tr></table></div></figure>
<p>#案例2：查询部门编号不是在90到110之间，或者工资高于15000的员工信息</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	<span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	<span class="keyword">NOT</span>(department_id<span class="operator">&gt;=</span><span class="number">90</span> <span class="keyword">AND</span>  department_id<span class="operator">&lt;=</span><span class="number">110</span>) <span class="keyword">OR</span> salary<span class="operator">&gt;</span><span class="number">15000</span>;</span><br></pre></td></tr></table></div></figure>

        <h3 id="三、模糊查询-1"   >
          <a href="#三、模糊查询-1" class="heading-link"><i class="fas fa-link"></i></a>三、模糊查询</h3>
      <p>/*<br>like</p>
<p>between and<br><br>in<br>is null | is not null</p>
<p><em>/<br>#1.like<br>/</em><br>特点：<br>①一般和通配符搭配使用<br>    通配符：<br>    % 任意多个字符,包含0个字符<br>    _ 任意单个字符<br>*、</p>
<p>#案例1：查询员工名中包含字符a的员工信息</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> </span><br><span class="line">	<span class="operator">*</span></span><br><span class="line"><span class="keyword">from</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">where</span></span><br><span class="line">	last_name <span class="keyword">like</span> <span class="string">&#x27;%a%&#x27;</span>;   #abc</span><br></pre></td></tr></table></div></figure>
<br>
#案例2：查询员工名中第三个字符为e，第五个字符为a的员工名和工资<br>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">	last_name,</span><br><span class="line">	salary</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	last_name <span class="keyword">LIKE</span> <span class="string">&#x27;__n_l%&#x27;</span>;</span><br></pre></td></tr></table></div></figure>
#案例3：查询员工名中第二个字符为_的员工名
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	last_name</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	last_name <span class="keyword">LIKE</span> <span class="string">&#x27;_$_%&#x27;</span> <span class="keyword">ESCAPE</span> <span class="string">&#x27;$&#x27;</span>;</span><br><span class="line">    # <span class="keyword">ESCAPE</span> <span class="string">&#x27;$&#x27;</span>,<span class="string">&#x27;$&#x27;</span>后面的<span class="string">&#x27;_&#x27;</span>当做普通字符<span class="string">&#x27;_&#x27;</span>使用</span><br></pre></td></tr></table></div></figure>
#2.between and<br>
/*
①使用between and 可以提高语句的简洁度<br>
②包含临界值
③两个临界值不要调换顺序

<p>*/</p>
<p>#案例1：查询员工编号在100到120之间的员工信息</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	<span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	employee_id <span class="operator">&gt;=</span> <span class="number">120</span> <span class="keyword">AND</span> employee_id<span class="operator">&lt;=</span><span class="number">100</span>;</span><br></pre></td></tr></table></div></figure>
<p>#———————-</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	<span class="operator">*</span></span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	employee_id <span class="keyword">BETWEEN</span> <span class="number">120</span> <span class="keyword">AND</span> <span class="number">100</span>;</span><br></pre></td></tr></table></div></figure>
<p>#3.in<br><br>/*<br>含义：判断某字段的值是否属于in列表中的某一项<br>特点：<br>    ①使用in提高语句简洁度<br>    ②in列表的值类型必须一致或兼容<br>    ③in列表中不支持通配符</p>
<p>*/<br>#案例：查询员工的工种编号是 IT_PROG、AD_VP、AD_PRES中的一个员工名和工种编号</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	last_name,</span><br><span class="line">	job_id</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	job_id <span class="operator">=</span> <span class="string">&#x27;IT_PROT&#x27;</span> <span class="keyword">OR</span> job_id <span class="operator">=</span> <span class="string">&#x27;AD_VP&#x27;</span> <span class="keyword">OR</span> JOB_ID <span class="operator">=</span><span class="string">&#x27;AD_PRES&#x27;</span>;</span><br></pre></td></tr></table></div></figure>
<p>#——————</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	last_name,</span><br><span class="line">	job_id</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	job_id <span class="keyword">IN</span>( <span class="string">&#x27;IT_PROT&#x27;</span> ,<span class="string">&#x27;AD_VP&#x27;</span>,<span class="string">&#x27;AD_PRES&#x27;</span>);</span><br></pre></td></tr></table></div></figure>
<p>#4、is null<br><br>/*<br>=或&lt;&gt;不能用于判断null值<br>is null或is not null 可以判断null值<br>*/</p>
<p>#案例1：查询没有奖金的员工名和奖金率</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	last_name,</span><br><span class="line">	commission_pct</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	commission_pct <span class="keyword">IS</span> <span class="keyword">NULL</span>;</span><br></pre></td></tr></table></div></figure>
<p>#案例1：查询有奖金的员工名和奖金率</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	last_name,</span><br><span class="line">	commission_pct</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	commission_pct <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>;</span><br></pre></td></tr></table></div></figure>

<p>#安全等于  &lt;=&gt;</p>
<p>#案例1：查询没有奖金的员工名和奖金率</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	last_name,</span><br><span class="line">	commission_pct</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"><span class="keyword">WHERE</span></span><br><span class="line">	commission_pct <span class="operator">&lt;=&gt;</span><span class="keyword">NULL</span>;</span><br></pre></td></tr></table></div></figure>

<p>#案例2：查询工资为12000的员工信息</p>
<figure class="highlight sql"><div class="table-container"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">SELECT</span></span><br><span class="line">	last_name,</span><br><span class="line">	salary</span><br><span class="line"><span class="keyword">FROM</span></span><br><span class="line">	employees</span><br><span class="line"></span><br><span class="line"><span class="keyword">WHERE</span> </span><br><span class="line">	salary <span class="operator">&lt;=&gt;</span> <span class="number">12000</span>;</span><br></pre></td></tr></table></div></figure>

<p>#is null pk &lt;=&gt;</p>
<p>IS NULL:仅仅可以判断NULL值，可读性较高，建议使用<br><br>&lt;=&gt;    :既可以判断NULL值，又可以判断普通的数值，可读性较低</p>
</div><footer class="post-footer"><div class="post-ending ending"><div class="ending__text">------ END ------</div></div><div class="post-copyright copyright"><div class="copyright-author"><span class="copyright-author__name">Author: </span><span class="copyright-author__value"><a href="https://gityang2016.gitee.io">Yangj</a></span></div><div class="copyright-link"><span class="copyright-link__name">Link: </span><span class="copyright-link__value"><a href="https://gityang2016.gitee.io/2021/01/27/mysql_01/">https://gityang2016.gitee.io/2021/01/27/mysql_01/</a></span></div><div class="copyright-notice"><span class="copyright-notice__name">Copyright: </span><span class="copyright-notice__value">All articles in this blog are licensed under <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.en" rel="external nofollow" target="_blank">BY-NC-SA</a> unless stating additionally</span></div></div><div class="post-tags"><span class="post-tags-item"><span class="post-tags-item__icon"><i class="fas fa-tag"></i></span><a class="post-tags-item__link" href="https://gityang2016.gitee.io/tags/sql/">sql</a></span></div><nav class="post-paginator paginator"><div class="paginator-prev"><a class="paginator-prev__link" href="/2021/01/27/JVM_01/"><span class="paginator-prev__icon"><i class="fas fa-angle-left"></i></span><span class="paginator-prev__text">JVM</span></a></div><div class="paginator-next"><a class="paginator-next__link" href="/2021/01/24/jsp_01/"><span class="paginator-prev__text">JSP 学习</span><span class="paginator-next__icon"><i class="fas fa-angle-right"></i></span></a></div></nav></footer></div></div></div><div class="sidebar-wrap" id="sidebar-wrap"><aside class="sidebar" id="sidebar"><div class="sidebar-nav"><span class="sidebar-nav-toc current">Catalog</span><span class="sidebar-nav-ov">Overview</span></div><section class="sidebar-toc"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link" href="#Mysql"><span class="toc-number">1.</span> <span class="toc-text">
          Mysql</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link" href="#DB"><span class="toc-number">1.1.</span> <span class="toc-text">
          DB</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#DBMS"><span class="toc-number">1.2.</span> <span class="toc-text">
          DBMS</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#SQL"><span class="toc-number">1.3.</span> <span class="toc-text">
          SQL</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#MySQL%E4%BA%A7%E5%93%81%E7%9A%84%E4%BB%8B%E7%BB%8D%E5%92%8C%E5%AE%89%E8%A3%85"><span class="toc-number">1.4.</span> <span class="toc-text">
          MySQL产品的介绍和安装</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#MySQL%E6%9C%8D%E5%8A%A1%E7%9A%84%E5%90%AF%E5%8A%A8%E5%92%8C%E5%81%9C%E6%AD%A2"><span class="toc-number">1.4.1.</span> <span class="toc-text">
          MySQL服务的启动和停止</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#MySQL%E6%9C%8D%E5%8A%A1%E7%9A%84%E7%99%BB%E5%BD%95%E5%92%8C%E9%80%80%E5%87%BA"><span class="toc-number">1.4.2.</span> <span class="toc-text">
          MySQL服务的登录和退出</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#MySQL%E7%9A%84%E5%B8%B8%E8%A7%81%E5%91%BD%E4%BB%A4"><span class="toc-number">1.5.</span> <span class="toc-text">
          MySQL的常见命令</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#SQL%E7%9A%84%E5%B8%B8%E8%A7%81%E5%91%BD%E4%BB%A4"><span class="toc-number">1.6.</span> <span class="toc-text">
          SQL的常见命令</span></a></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%BF%9B%E9%98%B61%EF%BC%9A%E5%9F%BA%E7%A1%80%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.7.</span> <span class="toc-text">
          进阶1：基础查询</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#1-%E6%9F%A5%E8%AF%A2%E8%A1%A8%E4%B8%AD%E7%9A%84%E5%8D%95%E4%B8%AA%E5%AD%97%E6%AE%B5"><span class="toc-number">1.7.1.</span> <span class="toc-text">
          1.查询表中的单个字段</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#2-%E6%9F%A5%E8%AF%A2%E8%A1%A8%E4%B8%AD%E7%9A%84%E5%A4%9A%E4%B8%AA%E5%AD%97%E6%AE%B5"><span class="toc-number">1.7.2.</span> <span class="toc-text">
          2.查询表中的多个字段</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#3-%E6%9F%A5%E8%AF%A2%E8%A1%A8%E4%B8%AD%E7%9A%84%E6%89%80%E6%9C%89%E5%AD%97%E6%AE%B5"><span class="toc-number">1.7.3.</span> <span class="toc-text">
          3.查询表中的所有字段</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#4-%E6%9F%A5%E8%AF%A2%E5%B8%B8%E9%87%8F%E5%80%BC"><span class="toc-number">1.7.4.</span> <span class="toc-text">
          4.查询常量值</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#5-%E6%9F%A5%E8%AF%A2%E8%A1%A8%E8%BE%BE%E5%BC%8F"><span class="toc-number">1.7.5.</span> <span class="toc-text">
          5.查询表达式</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#6-%E6%9F%A5%E8%AF%A2%E5%87%BD%E6%95%B0"><span class="toc-number">1.7.6.</span> <span class="toc-text">
          6.查询函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#7-%E8%B5%B7%E5%88%AB%E5%90%8D"><span class="toc-number">1.7.7.</span> <span class="toc-text">
          7.起别名</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#8-%E5%8E%BB%E9%87%8D"><span class="toc-number">1.7.8.</span> <span class="toc-text">
          8.去重</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#9-%E5%8F%B7%E7%9A%84%E4%BD%9C%E7%94%A8"><span class="toc-number">1.7.9.</span> <span class="toc-text">
          9.+号的作用</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link" href="#%E8%BF%9B%E9%98%B62%EF%BC%9A%E6%9D%A1%E4%BB%B6%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.8.</span> <span class="toc-text">
          进阶2：条件查询</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%80%E3%80%81%E6%8C%89%E6%9D%A1%E4%BB%B6%E8%A1%A8%E8%BE%BE%E5%BC%8F%E7%AD%9B%E9%80%89"><span class="toc-number">1.8.1.</span> <span class="toc-text">
          一、按条件表达式筛选</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E6%8C%89%E9%80%BB%E8%BE%91%E8%A1%A8%E8%BE%BE%E5%BC%8F%E7%AD%9B%E9%80%89"><span class="toc-number">1.8.2.</span> <span class="toc-text">
          二、按逻辑表达式筛选</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%89%E3%80%81%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%AF%A2"><span class="toc-number">1.8.3.</span> <span class="toc-text">
          三、模糊查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E7%A4%BA%E4%BE%8B%E4%B8%80%E3%80%81%E6%8C%89%E6%9D%A1%E4%BB%B6%E8%A1%A8%E8%BE%BE%E5%BC%8F%E7%AD%9B%E9%80%89"><span class="toc-number">1.8.4.</span> <span class="toc-text">
          示例一、按条件表达式筛选</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%BA%8C%E3%80%81%E6%8C%89%E9%80%BB%E8%BE%91%E8%A1%A8%E8%BE%BE%E5%BC%8F%E7%AD%9B%E9%80%89-1"><span class="toc-number">1.8.5.</span> <span class="toc-text">
          二、按逻辑表达式筛选</span></a></li><li class="toc-item toc-level-3"><a class="toc-link" href="#%E4%B8%89%E3%80%81%E6%A8%A1%E7%B3%8A%E6%9F%A5%E8%AF%A2-1"><span class="toc-number">1.8.6.</span> <span class="toc-text">
          三、模糊查询</span></a></li></ol></li></ol></li></ol></section><!-- ov = overview--><section class="sidebar-ov hide"><div class="sidebar-ov-author"><div class="sidebar-ov-author__avatar"><img class="sidebar-ov-author__avatar_img" src="/images/touxiang2.jpg" alt="avatar"></div><p class="sidebar-ov-author__text">Storms make trees take deeper roots</p></div><div class="sidebar-ov-social"><a class="sidebar-ov-social-item" href="https://github.com/Gityang2016" target="_blank" rel="noopener" data-popover="Github" data-popover-pos="up"><span class="sidebar-ov-social-item__icon"><i class="fab fa-github"></i></span></a></div><div class="sidebar-ov-state"><a class="sidebar-ov-state-item sidebar-ov-state-item--posts" href="/archives/"><div class="sidebar-ov-state-item__count">6</div><div class="sidebar-ov-state-item__name">Archives</div></a><a class="sidebar-ov-state-item sidebar-ov-state-item--categories" href="/categories/"><div class="sidebar-ov-state-item__count">8</div><div class="sidebar-ov-state-item__name">Categories</div></a><a class="sidebar-ov-state-item sidebar-ov-state-item--tags" href="/tags/"><div class="sidebar-ov-state-item__count">5</div><div class="sidebar-ov-state-item__name">Tags</div></a></div><div class="sidebar-ov-cc"><a href="https://creativecommons.org/licenses/by-nc-sa/4.0/deed.en" target="_blank" rel="noopener" data-popover="Creative Commons" data-popover-pos="up"><img src="/images/cc-by-nc-sa.svg"></a></div></section><div class="sidebar-reading"><div class="sidebar-reading-info"><span class="sidebar-reading-info__text">You have read </span><span class="sidebar-reading-info__num">0</span><span class="sidebar-reading-info__perc">%</span></div><div class="sidebar-reading-line"></div></div></aside></div><div class="clearfix"></div></div></main><footer class="footer" id="footer"><div class="footer-inner"><div><span>Copyright © 2021</span><span class="footer__icon"><i class="fas fa-heart"></i></span><span>Gityang2016</span></div><div><span>Powered by <a href="http://hexo.io/" title="Hexo" target="_blank" rel="noopener">Hexo</a></span><span> v5.3.0</span><span class="footer__devider">|</span><span>Theme - <a href="https://github.com/liuyib/hexo-theme-stun/" title="Stun" target="_blank" rel="noopener">Stun</a></span><span> v2.6.1</span></div></div></footer><div class="loading-bar" id="loading-bar"><div class="loading-bar__progress"></div></div><div class="back2top" id="back2top"><span class="back2top__icon"><i class="fas fa-rocket"></i></span></div></div><script src="https://cdn.jsdelivr.net/npm/jquery@v3.4.1/dist/jquery.min.js"></script><script src="https://cdn.jsdelivr.net/npm/velocity-animate@1.5.2/velocity.min.js"></script><script src="https://cdn.jsdelivr.net/npm/velocity-animate@1.5.2/velocity.ui.min.js"></script><script src="/js/utils.js?v=2.6.1"></script><script src="/js/stun-boot.js?v=2.6.1"></script><script src="/js/scroll.js?v=2.6.1"></script><script src="/js/header.js?v=2.6.1"></script><script src="/js/sidebar.js?v=2.6.1"></script></body></html>